<?php
/**
 * Created by PhpStorm.
 * User: coolinmind
 * Date: 07/11/2018
 * Time: 14:27
 */

namespace App\Services\Finance\Record;


use App\Models\Finance\Cost;
use App\Models\Finance\Income;
use App\Models\Finance\IncomeExcel;
use App\Models\Finance\Management;
use App\Models\Finance\ManagementType;
use App\Models\Finance\Monthstatis;
use DB;
use Excel;

class RecordLogic
{
    //添加成本
    public function addCost($data)
    {
        $date = $data['date'];
        $data['date'] = date('Y-m-d H:i:s', strtotime($date));
        $res = Cost::insertGetId($data);
        return auto_return($res);
    }

    //获取成本列表
    public function costList($data)
    {
        $res = Cost::where(['status' => 0])
            ->orderBy('date', 'desc')
            ->paginate(10);
        return auto_return($res);
    }

    //获取成本统计数据
    public function costStatistics($data)
    {
        $year = array_key_exists('year', $data) ? $data['year'] : date('Y');
        $sql = 'SELECT YEAR(date) year,MONTH(date) month,SUM(amount) total FROM cost WHERE YEAR(date) = ' . $year . ' GROUP BY year, month ORDER BY month';
        $res = DB::select($sql);
        $yearSearch = DB::select('SELECT DISTINCT YEAR(date) year FROM cost ORDER BY year ASC');
        $arr = [
            'data' => $res,
            'search' => $yearSearch,
            'selected' => $year
        ];
        return auto_return($arr);
    }

    //添加月收入报表
    public function addStatistics($data)
    {
        //判断月度报表是否已经存在
        $valiRes = Income::where(['year' => $data['year'], 'month' => $data['month']])->first();
        if ($valiRes)
            return auto_failure($valiRes, '该报表已添加');

        $type = ManagementType::with('platform')->get()->toArray();
        if (!$type)
            return auto_failure($type, '请添加sp分账管理类型');
        $finallArr = [];
        foreach ($type as $v) {
            $finallArr[] = [
                'year' => $data['year'],
                'month' => $data['month'],
                'pid' => $v['id'],
                'type_des' => $v['type'],
                'platform_id' => $v['platform']['id']
            ];
        }

        $res = Income::insert($finallArr);
        return $res ?
            auto_success($res, '添加成功') :
            auto_failure($res, '添加失败');
    }

    //按月查询成本数据
    public function costByMonth($data)
    {
        $year = $data['year'];
        $month = $data['month'];
        $where = 'WHERE YEAR(date) = ' . $year . ' && MONTH(date) = ' . $month;
        $sql = 'SELECT YEAR(date) year,MONTH(date) month,SUM(amount) total FROM cost ' . $where . ' GROUP BY year, month ORDER BY month';
        $cost = DB::select($sql);
        $sql1 = 'SELECT id,type,amount,remark,MONTH(date) month,date FROM cost ' . $where;
        $data = DB::select($sql1);

        $monthSearch = DB::select('SELECT DISTINCT MONTH(date) month FROM cost WHERE YEAR(date) = ' . $year .' ORDER BY month');

        $arr = [
            'total' => $cost[0]->total,
            'data' => $data,
            'monthSearch' => $monthSearch,
            'selected' => $month
        ];
        return auto_return($arr);
    }

    //添加音乐平台
    public function financeAddSp($data)
    {
        //判断音乐平台是否存在
        $validated = Management::where('platform', '=', $data['company'])->first();
        if ($validated) {
            return auto_failure($validated, '该平台已存在');
        }

        $arr['platform'] = $data['company'];
        $res = Management::insertGetId($arr);
        if (!$res)
            return auto_failure($res, '添加失败');
        return auto_success($res, '添加成功');
    }

    //删除音乐平台
    public function delSp($data)
    {
        //判断是否是删除单条
        if (array_key_exists('sonId', $data)) {
            $son = ManagementType::find($data['sonId']);
//            $this->_delDataFromIncome($data['sonId']);
            $res = $son ? $son->delete() : true;
        } else {
            //判断数据是否存在
            $valiRes = Management::find($data['id']);
            if ($valiRes) {
                //删除子分类
                ManagementType::where('pid', '=', $data['id'])->delete();
                $res = $valiRes->delete();
            } else {
                $res = true;
            }
        }
        return auto_success($res, '删除成功');

    }

    //给音乐平台增加类型
    public function addOne($data)
    {
        $res = Management::find($data['id']);
        if (!$res)
            return auto_failure($res, '该音乐平台不存在');

        $arr = ['pid' => $res->id, 'type' => $data['company']];
        $r = ManagementType::insertGetId($arr);
        $arr['id'] = $r;
        $res = $this->_addDataToIncome($arr);
        return $res ?
            auto_success($res, '添加成功') :
            auto_failure($res, '添加失败');
    }

    //初始化income表
    public function _addDataToIncome($data)
    {
        $arr = [
            'platform_id' => $data['pid'],
            'pid' => $data['id'],
            'type_des' => $data['type']
        ];

        $unCount = Income::select(DB::raw('DISTINCT year,month'))->where('status', '!=', 2)->get()->toArray();
        foreach ($unCount as &$v) {
            unset($v['status_text']);
            $v = array_merge($v, $arr);
        }

        $res = Income::insert($unCount);
        return $res;
    }

    //初始化income表
    public function _delDataFromIncome($id)
    {
        $unCount = Income::select(DB::raw('DISTINCT year,month'))->where('status', '!=', 2)->get()->toArray();
        foreach ($unCount as &$v) {
            unset($v['status_text']);
            $v['pid'] = $id;
            Income::where($v)->delete();
        }
    }

    //获取音乐平台及其对应的类型
    public function spList()
    {
        $list = Management::with('type')->paginate(5);
        return auto_return($list);
    }

    //获取收入报表模板
    public function spIncomeList()
    {
        $list = Income::select(DB::raw('distinct year,month'))->where('status','<>',2)->get()->toArray();
        foreach ($list as &$v) {
            $res = Income::where(['year' => $v['year'], 'month' => $v['month'], 'status' => 0])->first();
            $v['status'] = $res ? 0 : 1;
        }
        return auto_success($list);
    }

    //excel报表上传预加载
    public function spInputPrepare($data)
    {
        $_where = [
            'year' => $data['year'],
            'month' => $data['month']
        ];
        array_key_exists('platform_id', $data) && $data['platform_id'] && $_where['platform_id'] = $data['platform_id'];
        array_key_exists('type', $data) && $data['type'] && $_where['type_des'] = $data['type'];
        $list = Income::with('type')->with('platform')->where($_where)->paginate(10);
        //获取sp公司、收益类型
        $com = Management::get()->toArray();
        $type = ManagementType::select(DB::raw('DISTINCT type'))->get()->toArray();
        $arr = [
            'list' => $list,
            'search' => [
                'company' => $com,
                'type' => $type
            ]
        ];
        return auto_return($arr);
    }

    //执行excel报表上传
    public function spSubmit($data)
    {
        $_where = [
            'id' => $data['id']
        ];

        $arr = [
            'file_addr' => $data['file_addr'],
            'amount' => $data['amount'],
            'original_name' => $data['original_name'],
            'status' => 1
        ];

        //开启事务
        DB::beginTransaction();
        try {
            //读取excel
            Excel::load('.' . $data['file_addr'], function ($reader) use ($data) {
                $excelData = $reader->all()->toArray();
                $incomeArr = [];
                if ($excelData) {
                    foreach ($excelData as $v) {
                        $value = array_values($v);
                        $incomeArr[] = [
                            'income_id' => $data['id'],
                            'album' => $value[0] ?? '',
                            'title' => $value[1],
                            'performer' => $value[2],
                            'composer' => $value[3],
                            'writer_scale' => $value[4],
                            'composer_scale' => $value[5],
                            'play' => $value[6],
                        ];
                    }
                }
                IncomeExcel::insert($incomeArr);
            });
            //提交事务
            DB::commit();
        } catch (\Exception $e) {
            //事务回滚
            DB::rollback();
            return auto_failure($e->getMessage());
        }

        $res = Income::where($_where)->update($arr);
        return $res ?
            auto_success($res, '更新成功') :
            auto_failure($res, '更新失败');
    }

    //根据id删除单条excel报表数据
    public function delInputById($data)
    {
        $initData = [
            'amount' => 0,
            'file_addr' => '',
            'original_name' => '',
            'status' => 0
        ];

        //开启事务
        DB::beginTransaction();
        try {
            $res1 = Income::where(['id' => $data['id']])->update($initData);

            //删除excel数据
            $res2 = IncomeExcel::where(['income_id' => $data['id']])->delete();
            if($res1 && $res2)
            {
                DB::commit();
                return auto_success(null, '删除成功');
            }else{
                DB::rollBack();
                return auto_failure(null, '删除失败');
            }
        } catch (\Exception $e) {
            DB::rollBack();
            return auto_failure(null, $e->getMessage());
        }
    }

    //获取收入统计数据
    public function incomeStatistics()
    {
        $res = Monthstatis::select('*', DB::raw('sp_total - music_total - agent_total finall'))->paginate(10);
        $total = Monthstatis::select(DB::raw('sum(sp_total - music_total - agent_total) total'))->first()->toArray();
        $arr = [
            'list' => $res,
            'total' => $total['total']
        ];
        return auto_success($arr);
    }
}